﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;

namespace bdnt_pets_system_Bo
{
    public class AccessHelperODBC
    {
        private string strConnection { get; set; }
        private static AccessHelperODBC instance = null;
        public static AccessHelperODBC GetInstance()
        {
            if (instance == null)
                instance = new AccessHelperODBC();
            return instance;
        }

        public AccessHelperODBC()
        {
            strConnection = System.Configuration.ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
        }

        public DataTable ExcuteDataTable(string sql, OdbcParameter[] paras = null)
        {

            OdbcConnection objConnection = new OdbcConnection(strConnection);
            DataTable dt = new DataTable();
            try
            {
                objConnection.Open();
                OdbcDataAdapter da = new OdbcDataAdapter(sql, objConnection);
                if (paras != null)
                {
                    da.SelectCommand.Parameters.AddRange(paras);
                }
                da.Fill(dt);
                return dt;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public DataSet ExcuteDataSet(string sql, OdbcParameter[] paras = null)
        {
            OdbcConnection objConnection = new OdbcConnection(strConnection);
            DataSet ds = new DataSet();
            try
            {
                objConnection.Open();
                OdbcDataAdapter da = new OdbcDataAdapter(sql, objConnection);
                if (paras != null)
                {
                    da.SelectCommand.Parameters.AddRange(paras);
                }
                da.Fill(ds);
                return ds;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public bool ExcultData(string sql, OdbcParameter[] param = null)
        {
            OdbcConnection objConnection = new OdbcConnection(strConnection);
            OdbcCommand cmd = new OdbcCommand(sql, objConnection);
            try
            {

                if (objConnection.State == ConnectionState.Closed)
                { objConnection.Open(); }
                if (param != null)
                    cmd.Parameters.AddRange(param);
                return cmd.ExecuteNonQuery() > 0 ? true : false;
            }
            catch (OdbcException ob)
            {
                throw ob;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public int ExecuteNonQuery(string sql, OdbcParameter[] param = null)
        {
            OdbcConnection objConnection = new OdbcConnection(strConnection);
            OdbcCommand cmd = new OdbcCommand(sql, objConnection);
            try
            {
                if (param != null)
                    cmd.Parameters.AddRange(param);
                objConnection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (OdbcException ob)
            {
                throw ob;
            }
            finally
            {
                objConnection.Close();
            }
        }

        public object ExecuteScalar(string sql, OdbcParameter[] param = null)
        {
            OdbcConnection objConnection = new OdbcConnection(strConnection);
            OdbcCommand cmd = new OdbcCommand(sql, objConnection);
            try
            {
                if (param != null)
                    cmd.Parameters.AddRange(param);
                objConnection.Open();
                return cmd.ExecuteScalar();
            }
            catch (OdbcException ob)
            {
                throw ob;
            }
            finally
            {
                objConnection.Close();
            }
        }
    }
}
